import os
import xlrd
import pandas as pd

#获取所有需要合并的工作簿路径，生成list
def file_name(file_dir):
    list=[]
    for file in os.listdir(file_dir):
        if os.path.splitext(file)[1] == '.csv':
            list.append(file)
    return list
path = r'C:\Users\xys3817\Desktop\biaoge\hebing6666666'
wks = file_name(path)
print(wks)
data = []   #定义一个空list
for i in range(len(wks)):
    read_xlsx = xlrd.open_workbook(path + '\\' + wks[i])
    sheet1 = read_xlsx.sheets()[0] #查看sheet1的数据
    nrow =  sheet1.nrows
    title = sheet1.row_values(0)   #查看第1行数据
    for j in range(1,nrow): #逐行打印
        data.append(sheet1.row_values(j))
        print(sheet1.row_values(j))
content= pd.DataFrame(data)
#修改标题
content.columns= title
#写入文件#写入csv文件#content.to_csv(path+'\\py_union.xlsx', sep=',', header=True, index=False)
content.to_excel(path+'\\py_union26699999.xlsx', header=True, index=False)  #写入excel文件